﻿using System;
using System.Collections;
using System.Text;
using System.Data;
using GenaralLibs;
using System.Data.SqlClient;

namespace Lists
{
    public class LoaiGiayTo_NganhNghe : DoiTuong
    {
        #region int ID property
        public int ID
        {
            get
            {
                return (int)_DataRow["ID"];
            }
            set
            {
                _DataRow["ID"] = value;
            }
        }
        #endregion

        #region DMLoaiGiayTo LoaiGiayToID property
        public int LoaiGiayToID
        {
            get
            {
                return ((int)_DataRow["LoaiGiayToID"]);
            }
            set
            {
                _DataRow["LoaiGiayToID"] = value;
            }
        }
        #endregion

        #region DMNganhNghe NghanhNgheKinhDoanhId property
        public int NghanhNgheKinhDoanhId
        {
            get
            {
                return ((int)_DataRow["NghanhNgheKinhDoanhId"]);
            }
            set
            {
                _DataRow["NghanhNgheKinhDoanhId"] = value;
            }
        }
        #endregion

        public LoaiGiayTo_NganhNghe(DataRow dataRow)
            : base(dataRow)
        {
        }
    }

    public class DanhMucLoaiGiayTo_NganhNghe: DanhMucDoiTuong, IEnumerable
    {
        #region Instance property: Singleton Design pattern
        private static DanhMucLoaiGiayTo_NganhNghe _Instance;
        public static DanhMucLoaiGiayTo_NganhNghe Instance
        {
            get
            {
                if (_Instance == null)
                {
                    _Instance = new DanhMucLoaiGiayTo_NganhNghe();
                    _Instance.DataView = _Instance.DataTable.DefaultView;
                }
                return _Instance;
            }
        }
        #endregion

        #region override TableName property
        public override string TableName
        {
            get
            {
                return "LoaiGiayTo_NganhNghe";
            }
        }
        #endregion

        #region Methods required by IEnumerable (use for foreach)
        public IEnumerator GetEnumerator()
        {
            return new TokenEnumerator(this);
        }

        private class TokenEnumerator : IEnumerator
        {
            private int _Position = -1;
            DanhMucDoiTuong _Dstk;

            public TokenEnumerator(DanhMucDoiTuong dstk)
            {
                _Dstk = dstk;
            }

            // Declare the MoveNext method required by IEnumerator:
            public bool MoveNext()
            {
                if (_Position < _Dstk.DataView.Count - 1)
                {
                    _Position++;
                    return true;
                }
                else
                {
                    return false;
                }
            }

            // Declare the Reset method required by IEnumerator:
            public void Reset()
            {
                _Position = -1;
            }

            // Declare the Current property required by IEnumerator:
            public object Current
            {
                get
                {
                    return new LoaiGiayTo_NganhNghe((_Dstk.DataView[_Position].Row));
                }
            }
        }
        #endregion

        public LoaiGiayTo_NganhNghe this[int index]
        {
            get
            {
                if (0 <= index && index <= this.Count)
                {
                    return new LoaiGiayTo_NganhNghe(DataTable.Rows[index]);
                }
                else
                {
                    return null;
                }
            }
        }

        public LoaiGiayTo_NganhNghe NewDoiTuong()
        {
            try
            {
                return new LoaiGiayTo_NganhNghe(Instance.DataTable.NewRow());
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public LoaiGiayTo_NganhNghe GetByProperty(string property, object value)
        {
            try
            {
                DataRow[] rows = GetDataRows(property, value);
                if (rows.Length > 0) return new LoaiGiayTo_NganhNghe(rows[0]);
                else return null;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public LoaiGiayTo_NganhNghe GetByID(int objID)
        {
            try
            {
                return GetByProperty("ID", objID);
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public static void DeleteFromDB(int nganhNgheID)
        {
            SqlConnection con = new SqlConnection(GenaralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("DELETE FROM LoaiGiayTo_NganhNghe WHERE NghanhNgheKinhDoanhID = '{0}'",
                                                    nganhNgheID);

                cmd.ExecuteNonQuery();
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GenaralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }
        public DanhMucLoaiGiayTo_NganhNghe GetByLoaiGiayToID_NganhNgheKinhDoanhID(int loaiGiayTID, int nganhNgheKihnDoanhID)
        {
            try
            {
                return Filter(string.Format("LoaiGiayToID = {0} AND NghanhNgheKinhDoanhId = {1}", loaiGiayTID, nganhNgheKihnDoanhID));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public static DataTable GetLoaiHoSoByNganhNgheID(int nganhNgheID)
        {
            SqlConnection con = new SqlConnection(GenaralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();

                SqlCommand cmd = con.CreateCommand();
                DataSet ds = new DataSet();
                ds.Clear();
                cmd.CommandText = string.Format("Select * from DMLoaiGiayTo where ID in(select LoaiGiayToID from LoaiGiayTo_NganhNghe where NghanhNgheKinhDoanhId = '{0}')",
                                                    nganhNgheID);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                
                return ds.Tables[0];
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GenaralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }
        public DanhMucLoaiGiayTo_NganhNghe GetByNganhNgheID(int nganhNgheID)
        {
            try
            {
                return Filter(string.Format("NghanhNgheKinhDoanhID = {0}", nganhNgheID));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public DanhMucLoaiGiayTo_NganhNghe GetByLoaiGiayToID(int loaiGiayTID)
        {
            try
            {
                return Filter(string.Format("LoaiGiayToID = {0}", loaiGiayTID));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public DanhMucLoaiGiayTo_NganhNghe GetByNganhNgheKinhDoanhID(int nganhNgheKihnDoanhID)
        {
            try
            {
                return Filter(string.Format("NghanhNgheKinhDoanhId = {0}", nganhNgheKihnDoanhID));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public DataTable GetForQuanLyFile(int hoSoID, int nganhNgheKihnDoanhID)
        {
            SqlConnection con = new SqlConnection(LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                DataTable dt = new DataTable();
                cmd.CommandText = string.Format("SELECT *, N'Cập nhật' 'Text1', N'Xem' 'Text2' FROM ( \n" +
                                                "SELECT B.ID LoaiGiayTo_NganhNgheID, A.TenLoai, A.MoTa FROM DMLoaiGiayTo A INNER JOIN LoaiGiayTo_NganhNghe B ON A.ID = B.LoaiGiayToID  \n" +
                                                "INNER JOIN DMNghanhNghe D ON D.ID = B.NghanhNgheKinhDoanhId  \n" +
                                                "WHERE B.NghanhNgheKinhDoanhId = {0} \n" +
                                                ") as TBL1 LEFT JOIN ( \n" +
                                                "SELECT * FROM FileDinhKem WHERE HoSoID = {1}\n" +
                                                ") AS TBL2 ON TBL1.LoaiGiayTo_NganhNgheID = TBL2.LoaiGiayTo_NganhNgheID",
                                                nganhNgheKihnDoanhID, hoSoID);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }

        public static DanhMucLoaiGiayTo_NganhNghe Filter(string filterExpression)
        {
            try
            {
                DanhMucLoaiGiayTo_NganhNghe obj = new DanhMucLoaiGiayTo_NganhNghe();
                obj.DataView = new DataView(DanhMucLoaiGiayTo_NganhNghe.Instance.DataTable, filterExpression, "", DataViewRowState.CurrentRows);
                return obj;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static DanhMucLoaiGiayTo_NganhNghe GetAll()
        {
            try
            {
                return Instance;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public void DeleteOphansData()
        {
            SqlConnection con = new SqlConnection(LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("DELETE FROM FileDinhKem WHERE HoSoID NOT IN ( \n" +
                                                "SELECT ID FROM HoSoDangKy)");
                cmd.ExecuteNonQuery();
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }
    }
}
